package com.gitee.huanminabc.db_freedom.wrapper;

import com.gitee.huanminabc.jcommon.base.PlaceholderResolver;
import com.gitee.huanminabc.db_freedom.enums.SqlKeyword;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

@Data
public class SQL implements Serializable {
    private StringBuilder sql = new StringBuilder();
    private List<Object> params = new ArrayList<>();


    private SqlKeyword currentKeyword; //当前关键字 SELECT WHERE  FROM  GROUP  ORDER  HAVING  JOIN

    private  boolean isSelect; //是否是查询
    private  boolean isUpdate; //是否是更新
    private  boolean isInsert; //是否是插入
    private  boolean isDelete; //是否是删除

    public  final  static String SELECT_PLACEHOLDER="${selectColumns}";
    public  final  static String  GROUP_PLACEHOLDER="${groupColumns}";
    public  final  static String  ORDER_PLACEHOLDER="${orderColumns}";


    //sql 就这两个地方需要用到列字段
    private  List<SqlColumn> selectColumns=new ArrayList<>();
    private  List<SqlColumn> groupColumns=new ArrayList<>();
    private  List<SqlColumn> orderColumns=new ArrayList<>();

    public SQL set(SQL sql){
        this.sql=sql.sql;
        this.params=sql.params;
        this.currentKeyword=sql.currentKeyword;
        this.isSelect=sql.isSelect;
        this.isUpdate=sql.isUpdate;
        this.isInsert=sql.isInsert;
        this.isDelete=sql.isDelete;
        this.selectColumns=sql.selectColumns;
        this.groupColumns=sql.groupColumns;
        this.orderColumns=sql.orderColumns;
        return this;
    }

    //添加列


    public SQL addSelectColumn(String col){
        addSelectColumn(col,"","","","");
        return this;
    }
    public SQL addSelectColumnAlias(String col,String alias){
        addSelectColumn(col,"","","",alias);
        return this;
    }
    public SQL addSelectColumn(String table,String columnName){
        addSelectColumn("",table,columnName,"","");
        return this;
    }
    public SQL addSelectColumn(String table,String columnName,String alias){
        addSelectColumn("",table,columnName,"",alias);
        return this;
    }
    public SQL addSelectColumn(String  prefix,String table,String columnName,String  suffix,String alias){
        SqlColumn sqlColumn=new SqlColumn();
        sqlColumn.setColumnName(columnName);
        sqlColumn.setTableName(table);
        sqlColumn.setAlias(alias);
        sqlColumn.setPrefix(prefix);
        sqlColumn.setSuffix(suffix);

        selectColumns.add(sqlColumn);
        return this;
    }
    //添加组的列
    public SQL addGroupColumn(String col){
        addGroupColumn(col,"","","","");
        return this;
    }
    public SQL addGroupColumn(String table,String columnName){
        addGroupColumn("",table,columnName,"","");
        return this;
    }
    public SQL addGroupColumn(String  prefix,String table,String columnName,String  suffix,String alias){
        SqlColumn sqlColumn=new SqlColumn();
        sqlColumn.setColumnName(columnName);
        sqlColumn.setTableName(table);
        sqlColumn.setAlias(alias);
        sqlColumn.setPrefix(prefix);
        sqlColumn.setSuffix(suffix);
        groupColumns.add(sqlColumn);
        return this;
    }

    //添加排序的列
    public SQL addOrderColumn(String col){
        addOrderColumn(col,"","","","");
        return this;
    }
    public SQL addOrderColumn(String table,String columnName){
        addOrderColumn("",table,columnName,"","");
        return this;
    }
    public SQL addOrderColumn(String  prefix,String table,String columnName,String  suffix,String alias){
        SqlColumn sqlColumn=new SqlColumn();
        sqlColumn.setColumnName(columnName);
        sqlColumn.setTableName(table);
        sqlColumn.setAlias(alias);
        sqlColumn.setPrefix(prefix);
        sqlColumn.setSuffix(suffix);
        orderColumns.add(sqlColumn);
        return this;
    }


    public   String columnAll(){
        //如果是空的就跳过
        if(selectColumns.isEmpty()){
            return "*";
        }
        StringBuilder sb=new StringBuilder();
        for (SqlColumn selectColumn : selectColumns) {
            //除了第一个其他都是空
            if (selectColumn.getTableName().isEmpty()&&selectColumn.getColumnName().isEmpty()&&selectColumn.getAlias().isEmpty()&&selectColumn.getSuffix().isEmpty()){
                sb.append("   ").append(selectColumn.getPrefix()).append(",").append("\n");
                continue;
            }
            //除了第一个和别名其他都是空
            if (selectColumn.getTableName().isEmpty()&&selectColumn.getColumnName().isEmpty()&&selectColumn.getSuffix().isEmpty()){
                sb.append("   ").append(selectColumn.getPrefix()).append(" ").append(selectColumn.getAlias()).append(",").append("\n");
                continue;
            }

            sb.append("   ").append(selectColumn.getPrefix()).append(selectColumn.getTableName()).append(".").append(selectColumn.getColumnName()).append(selectColumn.getSuffix()).append(" ").append(selectColumn.getAlias()).append(",").append("\n");
        }
        //删除最后一个逗号
        sb.deleteCharAt(sb.length()-2);
        return sb.toString();
    }
    //获取指定表+列的column
    private SqlColumn getColumn(String table,String column){
        for (SqlColumn selectColumn : selectColumns) {
            if(selectColumn.getTableName().equals(table) && selectColumn.getColumnName().equals(column)){
                return selectColumn;
            }
        }
        return null;
    }

    private  String groupAll(){
        //如果是空的就跳过
        if(groupColumns.isEmpty()){
            return "";
        }
        StringBuilder sb=new StringBuilder();
        for (SqlColumn groupColumn : groupColumns) {
            //除了第一个其他都是空
            if (groupColumn.getTableName().isEmpty()&&groupColumn.getColumnName().isEmpty()&&groupColumn.getAlias().isEmpty()){
                sb.append(groupColumn.getPrefix()).append(",");
                continue;
            }

            //判断columnAll对应的列是否有别名,如果有别名就用别名,否则用列名
            SqlColumn column = getColumn(groupColumn.tableName, groupColumn.columnName);
            if(column!=null&&!column.getAlias().isEmpty()){
                sb.append(column.getAlias()).append(",");
            }else{
                sb.append(groupColumn.getPrefix()).append(groupColumn.getTableName()).append(".").append(groupColumn.getColumnName()).append(groupColumn.getSuffix()).append(" ").append(groupColumn.getAlias()).append(",");

            }

        }
        //删除最后一个逗号
        sb.deleteCharAt(sb.length()-1);
        return sb.toString();
    }


    private  String orderAll(){
        //如果是空的就跳过
        if(orderColumns.isEmpty()){
            return "";
        }
        StringBuilder sb=new StringBuilder();
        for (SqlColumn orderColumn : orderColumns) {
            sb.append(orderColumn.getPrefix()).append(orderColumn.getTableName()).append(".").append(orderColumn.getColumnName()).append(orderColumn.getSuffix()).append(" ").append(orderColumn.getAlias()).append(",");
        }
        //删除最后一个逗号
        sb.deleteCharAt(sb.length()-1);
        return sb.toString();
    }





    // 表.列名 别名
    @Data
    @NoArgsConstructor
    public static class   SqlColumn  implements Serializable{


        //前缀
        private String prefix;

        //表名 可以是表的别名
        private String tableName;
        //列名
        private String columnName;

        //后缀
        private String suffix;

        //别名
        private String alias;

        public SqlColumn(String tableName, String field) {
            this.tableName = tableName;
            this.columnName = field;
        }
        public SqlColumn(String prefix,String tableName, String field, String suffix, String alias) {
            this.prefix = prefix;
            this.tableName = tableName;
            this.columnName = field;
            this.suffix = suffix;
            this.alias = alias;
        }
    }






    public SQL addParam(Object param){
        params.add(param);
        return this;
    }

    public SQL addParamAll(List<Object> param) {
        params.addAll(param);
        return this;
    }

    public int  length(){
        return sql.length();
    }

    public SQL append(String str){
        sql.append(str);
        return this;
    }
    public SQL append(SQL sql){
        this.sql.append(sql.sql);
        return this;
    }
    public SQL append(Object obj){
        //这种就不添加了,传入的不能是对象否则就自行处理了
        return this;
    }
    public SQL append(int i){
        sql.append(i);
        return this;
    }
    public SQL append(long l){
        sql.append(l);
        return this;
    }
    public SQL append(char c){
        sql.append(c);
        return this;
    }
    public SQL append(boolean b){
        sql.append(b);
        return this;
    }
    public SQL append(float f){
        sql.append(f);
        return this;
    }
    public SQL append(double d){
        sql.append(d);
        return this;
    }
    public SQL append(char[] chars){
        sql.append(chars);
        return this;
    }
    public SQL append(char[] chars,int offset,int len){
        sql.append(chars,offset,len);
        return this;
    }
    public SQL append(CharSequence charSequence){
        sql.append(charSequence);
        return this;
    }
    public SQL append(CharSequence charSequence,int start,int end){
        sql.append(charSequence,start,end);
        return this;
    }

    public void deleteCharAt(int index){
        sql.deleteCharAt(index);
    }


    //返回sql语言和参数合并后的sql语句
    public String toString(){
        String stringSql=transitionSql(sql.toString());
        //将sql中的?替换成真实的参数
        for (Object param : params) {
            stringSql = stringSql.replaceFirst("\\?", param.toString());
        }
        return stringSql;
    }

    public String toSqlStructure(){
        return transitionSql(sql.toString());
    }
    private String  transitionSql(String sql){
        String columnAll = columnAll();
        String groupAll = groupAll();
        String orderAll = orderAll();

        String stringSql = sql;
        PlaceholderResolver defaultResolver = PlaceholderResolver.getDefaultResolver();
        stringSql= defaultResolver.resolveByKV(stringSql, SQL.SELECT_PLACEHOLDER, columnAll);
        stringSql= defaultResolver.resolveByKV(stringSql, SQL.GROUP_PLACEHOLDER, groupAll);
        stringSql= defaultResolver.resolveByKV(stringSql, SQL.ORDER_PLACEHOLDER, orderAll);
        return stringSql;
    }


}
